﻿Imports System.Data.SQLite
Imports System.IO

Public Class ClsSQLite
    Dim SQLconnect As New SQLite.SQLiteConnection()
    Dim SQLcommand As SQLiteCommand
    Public Shared Function SQLiteConnectionStr() As String
        'SQLiteConnectionStr = "Data Source=" & Application.StartupPath & "oppp.db" & ";"
        SQLiteConnectionStr = "Data Source=" & "D:\DotNet Project Wichian\oppp-correct-data-tools\NHSO_OPPP_Tool\oppp.db"
    End Function
    Public Shared ReadOnly Property ConnSqlite() As String
        Get
            ConnSqlite = SQLiteConnectionStr()
        End Get
    End Property

    Function GetSQLiteDataset(ByVal Strsql As String, _
           Optional ByVal DatasetName As String = "Dataset1", _
           Optional ByVal TableName As String = "Table") As DataSet
        Dim SQLiteConnq As New SQLite.SQLiteConnection
        SQLiteConnq.ConnectionString = ConnSqlite
        Dim DA As New SQLiteDataAdapter(Strsql, SQLiteConnq)
        Dim DS As New DataSet(DatasetName)
        Try
            DA.Fill(DS, TableName)
        Catch x1 As Exception
            Err.Raise(60002, , x1.Message)
            MsgBox(x1.Message.ToString)
        End Try
        Return DS
    End Function

    Public Function GetSQLiteDataTable(ByVal Strsql As String, _
    Optional ByVal TableName As String = "Table") As DataTable
        Dim SQLiteConnq As New SQLite.SQLiteConnection
        SQLiteConnq.ConnectionString = ConnSqlite
        Dim DA As New SQLiteDataAdapter(Strsql, SQLiteConnq)
        Dim DT As New DataTable(TableName)
        Try

            DA.Fill(DT)
        Catch x1 As Exception
            Err.Raise(60002, , x1.Message)
            MsgBox(x1.Message.ToString, MsgBoxStyle.Critical, "SQL Exception")
        End Try
        Return DT
    End Function



    Public Shared Function SQLiteExecuteScalar(ByVal cmdType As CommandType, ByVal cmdText As String, ByVal cmdParms As SQLiteParameter()) As Object
        Dim SQLiteConnq As New SQLite.SQLiteConnection
        SQLiteConnq.ConnectionString = ConnSqlite
        Dim Cmd As New SQLiteCommand
        Try
            SQLitePrepareCommand(Cmd, SQLiteConnq, cmdType, cmdText, cmdParms)
            Dim val As Object = Cmd.ExecuteScalar()
            Cmd.Parameters.Clear()
            Return val
        Catch ex As SQLiteException
            Throw New Exception("SQL Exception ", ex)
        Catch exx As Exception
            Throw New Exception("ExeculateScalar", exx)
        Finally
            Cmd.Connection.Close()
            Cmd = Nothing
        End Try
    End Function
    Public Shared Function SQLiteExecuteScalar(ByVal cmdType As CommandType, ByVal cmdText As String) As Object
        Dim SQLiteConnq As New SQLite.SQLiteConnection
        SQLiteConnq.ConnectionString = ConnSqlite
        Dim Cmd As New SQLiteCommand
        Try
            Dim val As Object = Cmd.ExecuteScalar()
            Cmd.Parameters.Clear()
            Return val
        Catch ex As SQLiteException
            Throw New Exception("SQL Exception ", ex)
        Catch exx As Exception
            Throw New Exception("ExeculateScalar", exx)
        Finally
            Cmd.Connection.Close()
            Cmd = Nothing
        End Try
    End Function

    Public Shared Function SQLitePrepareCommand(ByRef cmd As SQLiteCommand, ByRef conn As SQLiteConnection, ByRef cmdType As CommandType, ByRef cmdText As String, ByRef cmdParms As SQLiteParameter()) As Boolean
        If Not conn.State = ConnectionState.Open Then
            'MsgBox("Connection open")
            conn.Open()
        End If
        Try
            cmd.Connection = conn
            cmd.CommandText = cmdText
            cmd.Parameters.Clear()
            '  cmd.ParameterCheck = True
            cmd.CommandType = cmdType
            If Not (IsNothing(cmdParms)) Then
                Dim parm As SQLiteParameter
                For Each parm In cmdParms
                    cmd.Parameters.Add(parm)
                Next
            End If
        Catch ex As SQLiteException
            Throw New Exception("SQL Exception ", ex)
        Catch exx As Exception
            Throw New Exception("PrepareCommand : ", exx)
        End Try
    End Function
    Public Sub SQLiteCreateParam(ByRef Cmd As SQLiteCommand, ByVal StrType As String)
        'T:Text, M:Memo, Y:Currency, D:Datetime, I:Integer, S:Single, B:Boolean, P: Picture
        Dim i As Integer
        Dim j As String
        For i = 1 To Len(StrType)
            j = UCase(Mid(StrType, i, 1))
            Dim P1 As New SQLiteParameter
            P1.ParameterName = "@P" & i
            Select Case j
                Case "T"
                    P1.DbType = DbType.String
                Case "M"
                    P1.DbType = DbType.String
                Case "Y"
                    P1.DbType = DbType.Decimal
                Case "D"
                    P1.DbType = DbType.DateTime
                Case "I"
                    P1.DbType = DbType.Int64
                Case "S"
                    P1.DbType = DbType.Decimal
                Case "B"
                    P1.DbType = DbType.Boolean
                Case "P"
                    P1.DbType = DbType.Binary
            End Select
            Cmd.Parameters.Add(P1)
        Next
    End Sub
    Public Function SQLiteCreateCommand(ByVal Strsql As String) As SQLiteCommand
        Dim cmd As New SQLiteCommand(Strsql)
        Return cmd
    End Function
    Public Shared Function SQLiteExecQuery(ByVal ProcedureName As String, ByVal Parms As SQLiteParameter(), ByVal sqlconn As SQLiteConnection, Optional ByVal DatasetName As String = "Dataset1") As DataSet
        Dim dsDataSet As New DataSet(DatasetName)
        ' Configure the MySqlCommand object
        Dim _cmdSqlCommand As SQLiteCommand = New SQLiteCommand()

        With _cmdSqlCommand
            .CommandType = CommandType.StoredProcedure      'Set type to StoredProcedure
            .CommandText = ProcedureName                    'Specify stored procedure to run
            .Connection = sqlconn

            ' Clear any previous parameters from the Command object
            Call .Parameters.Clear()

            ' Loop through parmameter collection adding parameters to the command object
            If Not (Parms Is Nothing) Then
                For Each sqlParm As SQLiteParameter In Parms
                    _cmdSqlCommand.Parameters.Add(sqlParm)
                Next
            End If
        End With

        Dim _adpAdapter As SQLiteDataAdapter = New SQLiteDataAdapter()
        ' Configure Adapter to use newly created command object and fill the dataset.
        _adpAdapter.SelectCommand = _cmdSqlCommand
        _adpAdapter.Fill(dsDataSet)

        Return dsDataSet
    End Function

    Public Function SQLiteExecute(ByRef CmdStr As String) As Integer

        Dim SQLiteConnq As New SQLite.SQLiteConnection
        SQLiteConnq.ConnectionString = ConnSqlite
        Dim Cmd As New SQLiteCommand
        Cmd.CommandText = CmdStr
        Cmd.Connection = SQLiteConnq
        Dim X As Integer
        Try
            SQLiteConnq.Open()
            X = Cmd.ExecuteNonQuery()
        Catch
            X = -1
        Finally
            SQLiteConnq.Close()
        End Try
        Return X
    End Function
End Class
